package edu.example;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.Collection;
import java.util.Iterator;
import java.util.TreeMap;
import java.util.Vector;

import javax.swing.JOptionPane;

public class OldAppManagerCloud
{
	private static ApplicationManagerCloud instance = null;
	
	private static UploadAppDialog mainGUI;
	
	private static File ayDataFile;
	private static File ayMapFile;
	
	private static File otsDataFile;
	private static File otsMapFile;
	
	private boolean mapCheckFlag = false;
	
	//Useful data Headers
	private Vector<String> ayHeader =  new Vector<String>();
	
	//Useful data Headers
	private Vector<String> otsHeader =  new Vector<String>();
	
	//AY file Headers
	private Vector<String> ayDataHeader =  new Vector<String>();
	
	//OTS file Headers
	private Vector<String> otsDataHeader =  new Vector<String>();
	
	private Vector<String> dbAyDataHeader = new Vector<String>();
	
	private Vector<String> dbOTSDataHeader = new Vector<String>();
	
	
	private Vector<String> dbreader = new Vector<String>();
	
	private Application currentApplication= new Application();
	
	//Newly uploaded Applications
	private TreeMap<String, Application> applications = new TreeMap<String, Application>();
	
	//Applications that already exist in DB
	private TreeMap<String, Application> dbApplications = new TreeMap<String, Application>();
	
	//All applications including newly uploaded and in the DB
	private TreeMap<String, Application> allApplications = new TreeMap<String, Application>();
	
	//Newly uploaded OTS
	private TreeMap<String, OfficialTestScore> OTS = new TreeMap<String , OfficialTestScore>();
	
	//OTSs that already exist in DB
	private TreeMap<String, OfficialTestScore> dbOTS = new TreeMap<String, OfficialTestScore>();
	
	//All OTSs including newly uploaded and in the DB
	private TreeMap<String, OfficialTestScore> allOTS = new TreeMap<String, OfficialTestScore>();
	
	//For toString() only
	private Vector<Vector<String>> ayEntires = new Vector<Vector<String>>();
	
	//For toString() only
	private Vector<Vector<String>> otsEntires = new Vector<Vector<String>>();
	
	
	
	public Vector<String> 						getAYHeader()		{return ayDataHeader;}
	public boolean 								getFlag()			{return mapCheckFlag;}
	public TreeMap<String, Application> 		getAllApps()		{return allApplications;}
	public TreeMap<String, Application> 		getNewApps()		{return applications;}
	public TreeMap<String, OfficialTestScore>	getAllScore()		{return allOTS;}
	

	public static ApplicationManagerCloud 			getAppManager()
	{
		 if (instance == null) 
			 instance = new ApplicationManagerCloud(mainGUI, ayDataFile,ayMapFile,otsDataFile,otsMapFile);
		 return instance;
	 }
	

	

	public OldAppManagerCloud(UploadAppDialog intiGUI, 
								File initAYDataFile, 
								File initAYMapFile,
								File initOTSFile,
								File initOTSMapFile)
	{
		mainGUI = intiGUI;
		ayDataFile = initAYDataFile;
		ayMapFile = initAYMapFile;
		otsDataFile = initOTSFile;
		otsMapFile = initOTSMapFile;
		if(dbApplications.isEmpty())
		{
			ayDummyDatabaseRead();
			loadSettingData();
			//System.out.println("read ay db");
		}
		if(dbOTS.isEmpty())
		{
			OTSDummyDatabaseRead();
			//System.out.println("read ots db");
		}
		if(ayDataFile != null)
		{
			ayDataProcessing(ayDataFile,ayMapFile);
		}
		if(otsDataFile != null)
		{
			otsDataProcessing(otsDataFile,otsMapFile);
		}
	}
	
	
	//Overwrite the Method
	public void ayDummyDatabaseRead()
	{				
		File file = new File("AY-map.txt");
		int counter = 0;		
		try
		{
			FileInputStream fis = new FileInputStream(file);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			String strLine;
			while ((strLine = br.readLine()) != null)   
			{
				int endIndex = strLine.indexOf("->");
				if(endIndex > 0)
				{
					String mapTo = strLine.substring(endIndex+2);										
					dbAyDataHeader.add(counter, mapTo);
				}
				counter++;
			}
			din.close();
		}
		catch (Exception e)
		{			
			System.err.println("Error: " + e.getMessage());	
		}
		String reader = null;
		dbreader= new Vector<String>();
		TreeMap<String, String> CurrentData = new TreeMap<String, String>();
		try{
			Connection conn=ConnectionUtils.getConnection();	    
			int column=1;
                        
            PreparedStatement s=conn.prepareStatement("select * from aydata",
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
    	    ResultSet rs=s.executeQuery();
    	    reader = null;
            if(rs.next()){
            	rs.beforeFirst();
            	while(rs.next()){
            		CurrentData = new TreeMap<String, String>();
            		dbreader=new Vector<String>();            		            		
            		for(column=1;column<81;column++){
            				reader = rs.getString(column);
            				reader = new String(reader.getBytes("ISO-8859-1"),"GB2312");        	
            				dbreader.addElement(reader);    
            				
            	    		}
            		for(int i = 0; i < dbAyDataHeader.size(); i++)
					{
						CurrentData.put(dbAyDataHeader.get(i), dbreader.get(i));
						//System.out.println(ayDataHeader.get(i)+": "+dbreader.get(i));
					}
            		
            		Application newApp = new Application(CurrentData);
					newApp.setNumDesiredReviews(3);
					
					if(allApplications.containsKey(newApp.getEmailAddress()))
					{
						allApplications.put(newApp.getEmailAddress(), newApp);
					}
					else
					{
						//applications.put(newApp.getEmailAddress(), newApp);
						allApplications.put(newApp.getEmailAddress(), newApp);
					}
					System.out.println("read 1 ay from db");
            	}
            }
            
            rs.close();
	        conn.close();            
		}catch(Exception e){
    		e.printStackTrace();
    	}		 
	}
	
	//Over Write The Method
	public void OTSDummyDatabaseRead()
	{
		File file = new File("OTS-map.txt");
		int counter = 0;		
		try
		{
			FileInputStream fis = new FileInputStream(file);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			String strLine;
			while ((strLine = br.readLine()) != null)   
			{
				int endIndex = strLine.indexOf("->");
				if(endIndex > 0)
				{
					String mapTo = strLine.substring(endIndex+2);										
					dbOTSDataHeader.add(counter, mapTo);
				}
				counter++;
			}
			din.close();
		}
		catch (Exception e)
		{			
			System.err.println("Error: " + e.getMessage());	
		}
		String reader = null;
		dbreader= new Vector<String>();
		TreeMap<String, String> CurrentData = new TreeMap<String, String>();
		try{
			Connection conn=ConnectionUtils.getConnection();	    
			int column=1;
                        
            PreparedStatement s=conn.prepareStatement("select * from otsdata",
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
    	    ResultSet rs=s.executeQuery();
    	    reader = null;
            if(rs.next()){
            	rs.beforeFirst();
            	while(rs.next()){
            		CurrentData = new TreeMap<String, String>();
            		dbreader=new Vector<String>();            		            		
            		for(column=1;column<23;column++){
            				reader = rs.getString(column);
            				reader = new String(reader.getBytes("ISO-8859-1"),"GB2312");        	
            				dbreader.addElement(reader);                    
            	    		}            		
            		for(int i = 0; i < dbOTSDataHeader.size(); i++)
					{
						CurrentData.put(dbOTSDataHeader.get(i), dbreader.get(i));
						//System.out.println(dbOTSDataHeader.get(i)+": "+dbreader.get(i));
						
					}
					OfficialTestScore newScore = new OfficialTestScore(CurrentData);
					
					if(allOTS.containsKey(newScore.getEmailAddress()))
					{
						allOTS.put(newScore.getEmailAddress(), newScore);
					}
					else
					{
						//OTS.put(newScore.getEmailAddress(), newScore);
						allOTS.put(newScore.getEmailAddress(), newScore);
					}
					System.out.println("read 1 ots from db");
            	}
            }
            
            rs.close();
	        conn.close();            
		}catch(Exception e){
    		//e.printStackTrace();
    	}
	}
	
	public void loadSettingData(){
		try{
			Connection conn=ConnectionUtils.getConnection();	    
			int column=1;
            
            PreparedStatement s=conn.prepareStatement("select * from appinfo",
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
    	    ResultSet rs=s.executeQuery();
    	    String reader = null;
            if(rs.next()){
            	rs.beforeFirst();
            	while(rs.next()){
            		currentApplication= new Application();
            		dbreader=new Vector<String>();            		            		
            		for(column=1;column<10;column++){
            				reader = rs.getString(column);
            				reader = new String(reader.getBytes("ISO-8859-1"),"GB2312");        	
            				dbreader.addElement(reader);    
            				
            	    		}
            		Collection<Application> temp = allApplications.values();
	    			Iterator<Application> it = temp.iterator();
	    				
	    			while(it.hasNext())
	    			{
	    				Application tempApp = it.next();
	    				if(tempApp.getEmailAddress().equals(dbreader.elementAt(0))){
	            			currentApplication=tempApp;
	            			currentApplication.setTier(dbreader.elementAt(1));
	            			currentApplication.setSummary(dbreader.elementAt(1));
	            			currentApplication.setTOEFLComments(dbreader.elementAt(1));
	            			currentApplication.setNumDesiredReviews(Integer.parseInt(dbreader.elementAt(1)));
	            			currentApplication.setSBUID(dbreader.elementAt(1));
	            			currentApplication.setSBUGPA(dbreader.elementAt(1));
	            			currentApplication.setRelatedFile(dbreader.elementAt(1));
	            			currentApplication.setStatus(dbreader.elementAt(1));
	    				}
	    			}
            		
					
					System.out.println("read 1 appinfo from db");
            	}
            }            
            rs.close();
	        conn.close();            
		}catch(Exception e){
    		e.printStackTrace();
    	}		
	}
	
	public void updateSettingData(){
		if(!allApplications.isEmpty())
		{											
				
				try{
				    Connection conn=ConnectionUtils.getConnection();
				    Statement statement = conn.createStatement();
				    String sql = "delete from appinfo";
				    int delete = statement.executeUpdate(sql);
				    
				    
				    conn.setAutoCommit(false);
				    String checker ="";
				    
				    Collection<Application> temp = allApplications.values();
	    			Iterator<Application> it = temp.iterator();
	    				
	    			while(it.hasNext())
	    			{
	    				Application tempApp = it.next();
	    				PreparedStatement stmt = conn.prepareStatement("INSERT INTO appinfo VALUES ( ?,?,?,?,?  ,?,?,?,?)");
				        
	    				
				        stmt.setString(1,  tempApp.getEmailAddress());
				        stmt.setString(2,  tempApp.getTier());
				        stmt.setString(3,  tempApp.getSummary());
				        stmt.setString(4,  tempApp.getTOEFLComments());
				        stmt.setString(5,  tempApp.getNumDesiredReviews());
				        stmt.setString(6,  tempApp.getSBUID());
				        stmt.setString(7,  tempApp.getSBUGPA());
				        stmt.setString(8,  tempApp.getRelatedFile());
				        stmt.setString(9,  tempApp.getStatus());
	    				
				        checker=tempApp.getEmailAddress();
					    if(checker==null)
					    	stmt.setString(1,  "");
					    checker=tempApp.getTier();
					    if(checker==null)
					    	stmt.setString(2,  "");
					    checker=tempApp.getSummary();
					    if(checker==null)
					    	stmt.setString(3,  "");
					    checker=tempApp.getTOEFLComments();
					    if(checker==null)
					    	stmt.setString(4,  "");
					    checker=tempApp.getNumDesiredReviews();
					    if(checker==null)
					    	stmt.setString(5,  "");
					    checker=tempApp.getSBUID();
					    if(checker==null)
					    	stmt.setString(6,  "");
					    checker=tempApp.getSBUGPA();
					    if(checker==null)
					    	stmt.setString(7,  "");
					    checker=tempApp.getRelatedFile();
					    if(checker==null)
					    	stmt.setString(8,  "");
					    checker=tempApp.getStatus();
					    if(checker==null)
					    	stmt.setString(9,  "");
				        stmt.addBatch();
					    // submit the batch for execution
				        int[] updateCounts = stmt.executeBatch();
				        conn.commit(); 
				        System.out.println("app setting is updated in db");
	    			}
			    
		        
		        conn.close();
				}catch(Exception e){
		    		e.printStackTrace();
		    	}	
				
			
			
		}
	}
	public String toString()
	{
		String s = "";
		Collection<Application> temp = allApplications.values();
		Iterator<Application> it = temp.iterator();
			
		while(it.hasNext())
		{
			Application tempApp = it.next();
			Collection<String> tempV = tempApp.getData().values();
			Collection<String> tempK = tempApp.getData().keySet();
			Iterator<String> itK = tempK.iterator();
			Iterator<String> itV = tempV.iterator();
				
			while(itK.hasNext())
			{
				String tempKey = itK.next();
				String tempValue = itV.next();
				
				//Here is the key and value, WRITE IT BACK TO DATABASE
				s = s+(tempKey+": "+tempValue+"\n");
				//System.out.println(tempKey+": "+tempValue);
			}
			//System.out.println(" ");
		}
		
		
		Collection<OfficialTestScore> tempS = allOTS.values();
		Iterator<OfficialTestScore> itS = tempS.iterator();
			
		while(itS.hasNext())
		{
			OfficialTestScore tempScore = itS.next();
			Collection<String> tempV = tempScore.getData().values();
			Collection<String> tempK = tempScore.getData().keySet();
			Iterator<String> itK = tempK.iterator();
			Iterator<String> itV = tempV.iterator();
			
			while(itK.hasNext())
			{
				String tempKey = itK.next();
				String tempValue = itV.next();
				
				s = s+(tempKey+": "+tempValue+"\n");
				//Here is the key and value, WRITE IT BACK TO DATABASE
				//System.out.println(tempKey+": "+tempValue);
			}
			//System.out.println(" ");
		}
		return s;
	}
	
	public boolean mappingCheck(Vector<String> dataHeader, File mappingFile, String dataType)
	{
		int counter = 0;
		String dt = dataType;
		try
		{
			FileInputStream fis = new FileInputStream(mappingFile);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			String strLine;
			while ((strLine = br.readLine()) != null)   
			{
				int endIndex = strLine.indexOf("->");
				if(endIndex > 0)
				{
					String mapTo = strLine.substring(endIndex+2);
					String mapHeader = strLine.substring(0, endIndex);
					//System.out.println(mapHeader+" "+mapTo);
					if(mapHeader.equals(dataHeader.get(counter)))
					{
						if(!mapTo.contains("ignore"))
						{
							if(dt.equals("AY"))
							{
								dataHeader.set(counter, mapTo);
							}
							else if(dt.equals("OTS"))
							{
								dataHeader.set(counter, mapTo);
							}
						}
					}
					else
					{
						JOptionPane.showMessageDialog(mainGUI, "Error at line "+(counter+1)+" in "+dataType+" file.", "Mapping error",JOptionPane.ERROR_MESSAGE);
						return mapCheckFlag = false;
					}
				}
				counter++;
			}
			din.close();
		}
		catch (Exception e)
		{
			JOptionPane.showMessageDialog(mainGUI, dataType+" data and "+dataType+" Mapping file doesn't match", "Mapping error",JOptionPane.ERROR_MESSAGE);
			System.err.println("Error: " + e.getMessage());
			return mapCheckFlag = false;
		}		
		
		return mapCheckFlag = true;
	}
	
	public void otsDataProcessing(File dataFile, File mapFile)
	{
		int size = 0;
		int lineCounter = 0;
		String word[] = null;
		//System.out.println("or wrong here");
		try
		{
			FileInputStream fis = new FileInputStream(dataFile);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			String strLine;
			while ((strLine = br.readLine()) != null)   
			{
				TreeMap<String, String> CurrentData = new TreeMap<String, String>();
				Vector<String> CurrentDataEntry = new Vector<String>();
				Connection conn=ConnectionUtils.getConnection();
				CurrentDataEntry.setSize(size);
				
				//Get rid of ""
				strLine = strLine.replaceAll("\"", "");
				
				for(int i = 0; i < strLine.length(); i++)
				{
					word = strLine.split("	");
				}
				if(lineCounter == 0)
				{
					for(int i = 0; i < word.length; i++)
					{
						otsDataHeader.add(i, word[i].trim());
						size = otsDataHeader.size();
					}
					mappingCheck(otsDataHeader, mapFile,"OTS");
				}
				else
				{
					for(int i = 0; i < word.length; i++)
						CurrentDataEntry.add(i, word[i].trim());
					otsEntires.add(CurrentDataEntry);
					for(int i = 0; i < otsDataHeader.size(); i++)
					{
						CurrentData.put(otsDataHeader.get(i), CurrentDataEntry.get(i));
					}
					OfficialTestScore newScore = new OfficialTestScore(CurrentData);
					
					if(allOTS.containsKey(newScore.getEmailAddress()))
					{
						allOTS.put(newScore.getEmailAddress(), newScore);
					}
					else
					{
						OTS.put(newScore.getEmailAddress(), newScore);
						allOTS.put(newScore.getEmailAddress(), newScore);
					}
					PreparedStatement s=conn.prepareStatement("select count(*) from otsdata where email= ?");
					s.setString(1, word[2]);
				    ResultSet rs = s.executeQuery();
			   	    if(rs.next()){	    
					   	if(rs.getInt(1)>0){	  	    	    
					   	    PreparedStatement ss=conn.prepareStatement("DELETE from otsdata where email= ?");					   
					   	    ss.setString(1, word[2]);
					   	    int deletecount = ss.executeUpdate();
					   	    System.out.println(deletecount + " row of ots data is updated");
					   	    }	    	    
					   }
					String checker;
					conn.setAutoCommit(false);
					PreparedStatement stmt = conn.prepareStatement("INSERT INTO otsdata VALUES (?, ?,?,?,?,?, ?,?,?,?,?, " +
					               	 "?,?,?,?,?, ?,?,?,?,?, ?) ");
			        for(int i = 0; i < otsDataHeader.size(); i++){
			        	stmt.setString(i+1,  CurrentDataEntry.get(i));
			            checker=CurrentDataEntry.get(i);
		   	            if(checker==null)
					       stmt.setString(i+1,  "");
					    }
					stmt.addBatch();
					               
					       // submit the batch for execution
				    int[] updateCounts = stmt.executeBatch();
				    conn.commit(); 
				    rs.close();					               
				}
				lineCounter++;
			}
			din.close();
		}		
		catch (Exception e)
		{
			//System.err.println("Error: " + e.getMessage());
		}
	}
	
	public void ayDataProcessing(File dataFile, File mapFile)
	{
		int size = 0;
		int lineCounter = 0;
		String word[] = null;
		//System.out.println("wrong here");
		try
		{
			FileInputStream fis = new FileInputStream(dataFile);
			DataInputStream din = new DataInputStream(fis);
			BufferedReader br = new BufferedReader(new InputStreamReader(din));
			
			String strLine;
			while ((strLine = br.readLine()) != null)   
			{
				TreeMap<String, String> CurrentData = new TreeMap<String, String>();
				Vector<String> CurrentDataEntry = new Vector<String>();
				Connection conn=ConnectionUtils.getConnection();
				CurrentDataEntry.setSize(size);
				
				//Get rid of ""
				strLine = strLine.replaceAll("\"", "");
				
				for(int i = 0; i < strLine.length(); i++)
				{
					word = strLine.split("	");
				}
				if (lineCounter == 0)
				{
					for(int i = 0; i < word.length; i++)
					{
						ayDataHeader.add(i, word[i]);
						size = ayDataHeader.size();
					}
					mappingCheck(ayDataHeader, mapFile, "AY");
				}
				else
				{
					for(int i = 0; i < word.length; i++)
						CurrentDataEntry.add(i, word[i]);
					ayEntires.add(CurrentDataEntry);
					for(int i = 0; i < ayDataHeader.size(); i++)
					{
						CurrentData.put(ayDataHeader.get(i), CurrentDataEntry.get(i));
						//System.out.println(ayDataHeader.get(i)+": "+CurrentDataEntry.get(i));
					}
					Application newApp = new Application(CurrentData);
					newApp.setNumDesiredReviews(3);
					
					
					if(allApplications.containsKey(newApp.getEmailAddress()))
					{
						allApplications.put(newApp.getEmailAddress(), newApp);
					}
					else
					{
						applications.put(newApp.getEmailAddress(), newApp);
						allApplications.put(newApp.getEmailAddress(), newApp);
					}
					PreparedStatement s=conn.prepareStatement("select count(*) from aydata where email_address= ?");
					s.setString(1, word[14]);
				    ResultSet rs = s.executeQuery();
			   	    if(rs.next()){
					   	if(rs.getInt(1)>0){	  
					   		rs.close();
					   		PreparedStatement ss=conn.prepareStatement("DELETE from aydata where email_address= ?");
					   		ss.setString(1, word[14]);
					   		int deletecount = ss.executeUpdate();
					   		System.out.println(deletecount + " row of ay data is updated");
					   		}
			   	    	}										
			   	    conn.setAutoCommit(false);
			   	    String checker;
			   	    PreparedStatement stmt = conn.prepareStatement("INSERT INTO aydata VALUES (?, ?,?,?,?,?, ?,?,?,?,?, " +
			   	               	 "?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, " +
			   	               	 "?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?)");
			   	    for(int i = 0; i < ayDataHeader.size(); i++){
			   	        stmt.setString(i+1,  CurrentDataEntry.get(i));
			            checker=CurrentDataEntry.get(i);
			 	           if(checker==null)
			   	           	stmt.setString(i+1,  "");    	        
			   	       }
			   	    stmt.addBatch();
			   	       //System.out.println(CurrentDataEntry.size());
			   	       // submit the batch for execution
			   	    int[] updateCounts = stmt.executeBatch();
			   	    conn.commit(); 
			   	    conn.close();
				}
				lineCounter++;
			}
			din.close();
						 
		}
		catch (Exception e)
		{
			//System.err.println("Error: " + e.getMessage());
		}		 
	}
	
	public static void main(String[] args)
	{
		File file1 = new File("AY.txt");
		File file2 = new File("AY-map.txt");
		File file3 = new File("OTS.txt");
		File file4 = new File("OTS-map.txt");
		ApplicationManagerCloud formatter = new ApplicationManagerCloud(null, file1, file2, file3, file4);
		//System.out.println(formatter.toString());
		//System.out.println(formatter.getApps().get("maurice.wilkes@bmail.com").getApplicantLastName());
	}
}
